package cn.net.autocode.platform.providers.service;

import cn.hutool.core.util.StrUtil;
import cn.net.autocode.platform.constants.PlatformConstant;
import cn.net.autocode.platform.exception.PlatformQueryException;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import cn.net.autocode.platform.providers.rules.QueryRules;
import cn.net.autocode.dbManager.Dao;
import cn.net.autocode.dbManager.SqlWrapperBuilder;
import cn.net.autocode.dbManager.handler.PreparedStatementHandler;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class ProviderServ {

    private final Dao dictDao;

    public ProviderServ(@Qualifier("dictDao") Dao dictDao) {
        this.dictDao = dictDao;
    }

    public void resultDeal(JSONArray results, JSONArray rules){
        int size = results.size();
        int ruleSize = rules.size();
        for(int i=0;i<size;i++){
            JSONObject result = results.getJSONObject(i);
            for(int j=0;j<ruleSize;j++){
                JSONObject rule = rules.getJSONObject(j);
                String key = StrUtil.toCamelCase(rule.getString("fieldName"));
                String resultType = rule.getString("resultType");
                if(result.containsKey(key)){
                    Object value = result.get(key);
                    if(value instanceof String){
                        String strValue = value.toString().trim();
                        if("StrArray".equals(resultType)){
                            if(strValue.startsWith("[")&&strValue.endsWith("]")){
                                strValue=strValue.substring(1,strValue.length()-1);
                            }
                            String[] strArray = strValue.split(",");

                            String[] newArray = new String[strArray.length];
                            for(int index=0;index<strArray.length;index++){
                                if(strArray[index]!=null) {
                                    String indexValue = strArray[index].trim();
                                    if(indexValue.startsWith("\"")&&indexValue.endsWith("\"")){
                                        newArray[index] = indexValue.substring(1,indexValue.length()-1);
                                    }else{
                                        newArray[index] = indexValue;
                                    }
                                }
                            }
                            result.put(key,newArray);
                        }else if("IntArray".equals(resultType)){
                            if(strValue.startsWith("[")&&strValue.endsWith("]")){
                                strValue=strValue.substring(1,strValue.length()-1);
                            }
                            String[] strArray = strValue.split(",");

                            Integer[] newArray = new Integer[strArray.length];
                            for(int index=0;index<strArray.length;index++){
                                if(strArray[index]!=null) {
                                    try {
                                        newArray[index] = Integer.parseInt(strArray[index].trim());
                                    } catch (NumberFormatException e) {
                                        newArray[index]=0;
                                    }
                                }
                            }
                            result.put(key,newArray);
                        }else if("JSONArray".equals(resultType)){
                            try {
                                if(StrUtil.isNotBlank(strValue)){
                                    JSONArray newArray = JSONArray.parseArray(strValue);
                                    result.put(key,newArray);
                                }else{
                                    result.put(key,new JSONArray());
                                }
                            } catch (Exception e) {
                                log.info(e.getMessage());
                            }
                        }else if("JSONObject".equals(resultType)){
                            try {
                                if(StrUtil.isNotBlank(strValue)){
                                    JSONObject newArray = JSONObject.parseObject(strValue);
                                    result.put(key,newArray);
                                }else{
                                    result.put(key,new JSONObject());
                                }

                            } catch (Exception e) {
                                log.info(e.getMessage());
                            }
                        }
                    }
                }
            }
        }
    }

    public SqlWrapperBuilder queryInit(JSONObject tableJson, JSONObject data) {

        JSONArray rules = dictDao.queryJSONArray("select * from ac_table_rules where rule_type in (31,32) and table_id=?", tableJson.getString("id"));

        //查询表名
        String orderColumn = null;
        String selectColumn = "*";
        String tableName = tableJson.getString("tableName");
        if(!tableJson.isEmpty()){
            orderColumn = tableJson.getString("orderColumn");
            selectColumn = tableJson.getString("selectColumn");
        }

        SqlWrapperBuilder swb = new SqlWrapperBuilder(tableName);
        swb.selectColumn(selectColumn);


        //查询前置规则,模糊匹配，默认条件
        QueryRules.preHandle(rules,data,swb,null);

        //参数绑定
        JSONArray columnsArray = this.columnsMsg(tableJson.getString("id"));
        int size=columnsArray.size();
        for(int i=0;i<size;i++){
            JSONObject column = columnsArray.getJSONObject(i);
            String fieldName = column.getString("fieldName");
            String columnName = column.getString("columnName");
            if(columnName.toLowerCase().trim().equals("sort_no")&& StrUtil.isBlank(orderColumn)){
                orderColumn="sort_no";
            }

            Object value = null;
            if(data.containsKey(fieldName)){
                value = data.get(fieldName);
            }

            if(value != null&&!value.equals("")){
                if(value instanceof List<?> listValue){
                    String columnType = column.getString("type");
                    if ("DATETIME".equals(columnType)&&listValue.size()==2) {
                        String startStrValue = listValue.get(0).toString();
                        String endStrValue = listValue.get(1).toString();
                        if(startStrValue.length()==10 && endStrValue.length()==10){
                            LocalDateTime startTime = LocalDate.parse(startStrValue, DateTimeFormatter.ofPattern("yyyy-MM-dd")).atStartOfDay();
                            LocalDateTime endTime = LocalDate.parse(endStrValue, DateTimeFormatter.ofPattern("yyyy-MM-dd")).plusDays(1).atStartOfDay().minusNanos(1000000);
                            swb.between(columnName, startTime, endTime);
                        }else{
                           swb.in(columnName, listValue);
                        }
                    }else{
                        swb.in(columnName, listValue);
                    }

                }else if(value instanceof String){
                    String lowFlag = value.toString().trim().toLowerCase();
                    if("@now".equals(lowFlag)){
                        swb.eq(columnName, PlatformConstant.dateValue(value.toString()));
                    }else{
                        swb.eq(columnName, PlatformConstant.strValue(value.toString()));
                    }
                }else{
                    swb.eq(columnName,value);
                }
            }
        }

        if(StrUtil.isNotBlank(orderColumn)){
            swb.orderBy(orderColumn);
        }else{
            for(int i=0;i<size;i++){
                JSONObject column = columnsArray.getJSONObject(i);
                String primaryKey = column.getString("primaryKey");
                String columnName = column.getString("columnName");
                if("Y".equals(primaryKey)){
                    if(StrUtil.isBlank(orderColumn)){
                        orderColumn = columnName;
                    }else{
                        orderColumn = orderColumn+","+columnName;
                    }
                }
            }
            swb.orderBy(orderColumn);
        }

        return swb;
    }

    public SqlWrapperBuilder existsInit(JSONObject mainTable,JSONObject refTable, JSONObject data) throws PlatformQueryException {
        //检查两个表是否有关联关系
        Map<String,String> refs = this.queryTableRefs(mainTable.getString("id"),refTable.getString("tableName"));
        if(refs.isEmpty()){
            throw new PlatformQueryException("没有设置表"+mainTable.getString("tableName")+","+refTable.getString("tableName")+"的关联关系，不能进行关联查询");
        }

        //第一步，建立主表查询条件及排序条件 select t1.*,t2.列名 from ref_table t1 join main_table t2 on , order by
        String tableName =  refTable.getString("tableName") +" t1 join "+mainTable.getString("tableName")+" t2 on ";

        //第二步：设置关联信息
        StringBuilder joinBuilder = new StringBuilder();
        for(Map.Entry<String,String> entry:refs.entrySet()){
            if(joinBuilder.isEmpty()){
                joinBuilder.append("t1.").append(entry.getValue()).append("=").append("t2.").append(entry.getKey());
            }else{
                joinBuilder.append(" and ").append("t1.").append(entry.getValue()).append("=").append("t2.").append(entry.getKey());
            }
        }
        tableName = tableName+ joinBuilder;

        //设置查询字段
        StringBuilder selectColumn = new StringBuilder("t1.*");
        List<String> refColumnsList = this.columnsList(refTable.getString("id"));
        List<String> mainColumnsList = this.columnsList(mainTable.getString("id"));
        for (String column:mainColumnsList){
            if(!refColumnsList.contains(column)){
                selectColumn.append(",t2.").append(column);
            }
        }

        //设置排序信息
        StringBuilder orderColumn = new StringBuilder();
        String mainOrderColumn = mainTable.getString("orderColumn");
        if(StrUtil.isNotBlank(mainOrderColumn)){
            String[] columns = mainOrderColumn.split(",");
            for(String column:columns){
                if(orderColumn.isEmpty()){
                    orderColumn.append("t2.").append(column);
                }else{
                    orderColumn.append(",t2.").append(column);
                }
            }
        }

        String refOrderColumn = refTable.getString("orderColumn");
        if(StrUtil.isNotBlank(refOrderColumn)){
            String[] columns = refOrderColumn.split(",");
            for(String column:columns){
                if(orderColumn.isEmpty()){
                    orderColumn.append("t1.").append(column);
                }else{
                    orderColumn.append(",t1.").append(column);
                }
            }
        }

        //获取关联表的所有列信息
        SqlWrapperBuilder swb = new SqlWrapperBuilder(tableName);
        swb.selectColumn(selectColumn.toString());


        /*
          子表处理
         */
        //查询前置规则,模糊匹配
        JSONArray refRules = dictDao.queryJSONArray("select * from ac_table_rules where rule_type = 32 and table_id=?", refTable.getString("id"));
        QueryRules.preHandle(refRules,data,swb,"t1");


        //数据库表字段绑定
        JSONArray refColumnsArray = this.columnsMsg(refTable.getString("id"));
        int refSize=refColumnsArray.size();
        for(int i=0;i<refSize;i++){
            JSONObject column = refColumnsArray.getJSONObject(i);
            String fieldName = column.getString("fieldName");
            String columnName = column.getString("columnName");
            if(orderColumn.isEmpty()&&columnName.equals("sort_no")){
                orderColumn.append("t1.sort_no");
            }
            Object value = null;
            if(data.containsKey(fieldName)){
                value = data.get(fieldName);
                data.remove(fieldName);
            }

            if(value != null&&!value.equals("")){
                if(value instanceof  List<?> valueList){
                    swb.in("t1."+columnName,valueList);
                }else if(value instanceof String){
                    String lowFlag = value.toString().trim().toLowerCase();
                    if("@now".equals(lowFlag)){
                        swb.eq("t1."+columnName, PlatformConstant.dateValue(value.toString()));
                    }else{
                        swb.eq("t1."+columnName, PlatformConstant.strValue(value.toString()));
                    }
                }else{
                    swb.eq("t1."+columnName,value);
                }
            }
        }


        /*
          主表处理
         */
        JSONArray mainRules = dictDao.queryJSONArray("select * from ac_table_rules where rule_type = 32 and table_id=?", mainTable.getString("id"));
        QueryRules.preHandle(mainRules,data,swb,"t2");


        JSONArray mainColumnsArray = this.columnsMsg(mainTable.getString("id"));
        int mainSize = mainColumnsArray.size();
        for(int i=0;i<mainSize;i++){
            JSONObject column = mainColumnsArray.getJSONObject(i);
            String fieldName = column.getString("fieldName");
            String columnName = column.getString("columnName");
            if(orderColumn.isEmpty()&&columnName.equals("sort_no")){
                orderColumn.append("t2.sort_no");
            }
            Object value = null;
            if(data.containsKey(fieldName)){
                value = data.get(fieldName);
                data.remove(fieldName);
            }

            if(value != null&&!value.equals("")){
                if(value instanceof  List<?> valueList){
                    swb.in("t2."+columnName,valueList);
                }else if(value instanceof String valueStr){
                    String lowFlag = valueStr.trim().toLowerCase();
                    if("@now".equals(lowFlag)){
                        swb.eq("t2."+columnName, PlatformConstant.dateValue(value.toString()));
                    }else{
                        swb.eq("t2."+columnName, PlatformConstant.strValue(value.toString()));
                    }
                }else{
                    swb.eq("t2."+columnName,value);
                }
            }
        }

        swb.orderBy(orderColumn.toString());
        return swb;
    }


    public JSONArray columnsMsg(String tableId) {
        String selectSql = "select * FROM ac_table_fields where table_id=?";
        JSONArray columns = dictDao.queryJSONArray(selectSql,tableId);
        int cols = columns.size();
        for(int col = 0; col < cols; col++) {
            JSONObject json = columns.getJSONObject(col);
            String columnName = json.getString("name").toLowerCase();
            String fieldName = PreparedStatementHandler.columnToProperty(columnName);
            json.put("columnName",columnName);
            json.put("fieldName",fieldName);
        }
        return columns;
    }

    public List<String> columnsList(String tableId) {
        String selectSql = "select name from ac_table_fields where table_id=?";
        JSONArray columns = dictDao.queryJSONArray(selectSql,tableId);
        List<String> columnList = new ArrayList<>();
        int cols = columns.size();
        for(int col = 0; col < cols; col++) {
            JSONObject json = columns.getJSONObject(col);
            String columnName = json.getString("name").toLowerCase().trim();
            columnList.add(columnName);
        }
        return columnList;
    }

    public Map<String,String> queryTableRefs(String tableId,String childrenTable){
        JSONArray childrenTables = dictDao.queryJSONArray("select * from ac_table_ref where table_id=? and fk_table=?",tableId,childrenTable);
        //按table进行分组
        Map<String,String> refs = new HashMap<>();
        int size = childrenTables.size();
        for(int i=0;i<size;i++){
            JSONObject refTable = childrenTables.getJSONObject(i);
            refs.put(refTable.getString("pkField"),refTable.getString("fkField"));
        }
        return refs;
    }
}
